package com.zhangtai.modules.controller.admin;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.zhangtai.modules.constVo.SecurityConst;
import com.zhangtai.modules.controller.aouthLogin.LoginController;
import com.zhangtai.modules.dao.*;
//import com.zhangtai.modules.dao.ssqlDao.JobDaoMssql;
import com.zhangtai.modules.dto.*;
import com.zhangtai.modules.entity.employee.EmployeeInfoEntity;
import com.zhangtai.modules.entity.employee.EmployeeRecruitmentEntity;
import com.zhangtai.modules.entity.regular.RegularBpmEntity;
import com.zhangtai.modules.entity.system.ContractSubGroupEntity;
import com.zhangtai.modules.jobTask.qw.Department;
import com.zhangtai.modules.jobTask.qw.QwUserEntity;
import com.zhangtai.modules.service.baseFromMssql.BaseMssqlServiceImpl;
import com.zhangtai.modules.service.employee.EmployeeInfoService;
import com.zhangtai.modules.service.employee.EmployeeRecruitmentService;
import com.zhangtai.modules.service.employee.EmployeeSecurityService;
import com.zhangtai.modules.service.regular.RegularBpmService;
import com.zhangtai.modules.service.security.SecurityService;
import com.zhangtai.modules.vo.*;
import com.zhangtai.utils.*;
import com.zhangtai.utils.Excel.ExcelUtil;
import io.netty.util.internal.StringUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiModelProperty;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.client.RestTemplate;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;

@RestController
@RequestMapping("export")
@Slf4j
@Api(tags = "表单导出接口")
public class ExportController {

    @Autowired
    private ExportDao exportDao;
    @Autowired
    private LoginController loginController;
    @Autowired
    private SecurityChangeDao securityChangeDao;
    @Autowired
    private JobController jobController;
    @Autowired
    private BaseMssqlServiceImpl baseMssqlService;

    @Autowired
    private SecurityRecordDao securityRecordDao;

    @Autowired
    private FundsRecordDao fundsRecordDao;

    @Autowired
    private SecurityService securityService;

    @Autowired
    private SecurityDao securityDao;

    @Autowired
    private EmployeeInfoService employeeInfoService;
    @Autowired
    private EmployeeSecurityService employeeSecurityService;
    @Autowired
    private EmployeeRecruitmentService employeeRecruitmentService;

    @Autowired
    private RegularBpmService regularBpmService;

    @GetMapping("/exportNewEntry")
    @ApiOperation(value = "入职员工列表")
    @PreAuthorize("hasAuthority('emp:rydc:update')")
    public void exportNewEntry(HttpServletResponse response, @RequestParam(value = "code", required = false) String code,
                               @RequestParam(value = "name", required = false) String name,
                               @RequestParam(value = "orgname", required = false) String orgname,
                               @RequestParam(value = "unitname", required = false) String unitname,
                               @RequestParam(value = "jobname", required = false) String jobname,
                               @RequestParam(value = "isReinstate", required = false) Integer isReinstate,
                               @RequestParam(value = "isEntryStatus", required = false) Integer isEntryStatus,
                               HttpServletRequest request) throws Exception {

        //获取当前用户角色下公司Id
        R checkR = loginController.checkOrgPermission(request, null, false);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<Long> orgIds = (List<Long>) checkR.getData();
        ArrayList<Integer> integers = new ArrayList<>();
        integers.add(0);
        integers.add(1);
        List employeeInfo = exportDao.getExportEmployeeInfo(code, name, orgname, unitname, jobname, integers, isReinstate, orgIds, isEntryStatus);
        for (int i = 0; i < employeeInfo.size(); i++) {
            Map map = (Map) employeeInfo.get(i);
            if (!ObjectUtils.isEmpty(map.get("nativeplace"))) {
                map.put("nativeplace", jobController.nativeplace.get(Long.parseLong(map.get("nativeplace").toString())));
            }
            if (!ObjectUtils.isEmpty(map.get("nation"))) {
                map.put("nation", jobController.nationality.get(map.get("nation").toString()));
            }
            if (!ObjectUtils.isEmpty(map.get("job_id"))) {
                map.put("jobname", jobController.jobMap.get(Long.parseLong(map.get("job_id").toString())));
            }
            if (!ObjectUtils.isEmpty(map.get("unit_id"))) {
                map.put("unitname", jobController.orgMap.get(Long.parseLong(map.get("unit_id").toString())));
            }
            if (!ObjectUtils.isEmpty(map.get("org_id"))) {
                map.put("orgname", jobController.orgMap.get(Long.parseLong(map.get("org_id").toString())));
            }
        }
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, employeeInfo, ExportEmployeeInfoDto.class, true);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "入职员工";
        outputFile2007(response, wb, excelName);
    }

    @GetMapping("/exportEducation")
    @ApiOperation(value = "员工教育经历列表")
   /* @PreAuthorize("hasAuthority('emp:rydc:update')")*/
    public void exportEducation(HttpServletResponse response, HttpServletRequest request,
                                @RequestParam(value = "code", required = false) String code,
                                @RequestParam(value = "name", required = false) String name,
                                @RequestParam(value = "orgname", required = false) String orgname,
                                @RequestParam(value = "unitname", required = false) String unitname,
                                @RequestParam(value = "jobname", required = false) String jobname,
                                @RequestParam(value = "isReinstate", required = false) Integer isReinstate,
                                @RequestParam(value = "isEntryStatus", required = false) Integer isEntryStatus) throws Exception {
        //获取当前用户角色下公司Id
        R checkR = loginController.checkOrgPermission(request, null, false);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<Long> orgIds = (List<Long>) checkR.getData();
        ArrayList<Integer> integers = new ArrayList<>();
        integers.add(0);
        integers.add(1);
        List employeeInfo = exportDao.getExportEducation(code, name, orgname, unitname, jobname, integers, isReinstate, orgIds, isEntryStatus);
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, employeeInfo, ExportEducationDto.class, true);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "员工教育经历";
        outputFile2007(response, wb, excelName);
    }


    @GetMapping("/exportSociety")
    @ApiOperation(value = "员工社会经历列表")
    @PreAuthorize("hasAuthority('emp:rydc:update')")
    public void exportSociety(HttpServletResponse response, HttpServletRequest request,
                              @RequestParam(value = "code", required = false) String code,
                              @RequestParam(value = "name", required = false) String name,
                              @RequestParam(value = "orgname", required = false) String orgname,
                              @RequestParam(value = "unitname", required = false) String unitname,
                              @RequestParam(value = "jobname", required = false) String jobname,
                              @RequestParam(value = "isReinstate", required = false) Integer isReinstate,
                              @RequestParam(value = "isEntryStatus", required = false) Integer isEntryStatus) throws Exception {
        //获取当前用户角色下公司Id
        R checkR = loginController.checkOrgPermission(request, null, false);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<Long> orgIds = (List<Long>) checkR.getData();
        ArrayList<Integer> integers = new ArrayList<>();
        integers.add(0);
        integers.add(1);
        List employeeInfo = exportDao.getExportSociety(code, name, orgname, unitname, jobname, integers, isReinstate, orgIds, isEntryStatus);
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, employeeInfo, ExportSocietyexperienceDto.class, true);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "员工社会经历";
        outputFile2007(response, wb, excelName);
    }

    @GetMapping("/exportInorgname")
    @ApiOperation(value = "员工政治面貌")
    @PreAuthorize("hasAuthority('emp:rydc:update')")
    public void exportInorgname(HttpServletResponse response, HttpServletRequest request,
                                @RequestParam(value = "code", required = false) String code,
                                @RequestParam(value = "name", required = false) String name,
                                @RequestParam(value = "orgname", required = false) String orgname,
                                @RequestParam(value = "unitname", required = false) String unitname,
                                @RequestParam(value = "jobname", required = false) String jobname,
                                @RequestParam(value = "isReinstate", required = false) Integer isReinstate,
                                @RequestParam(value = "isEntryStatus", required = false) Integer isEntryStatus) throws Exception {
        //获取当前用户角色下公司Id
        R checkR = loginController.checkOrgPermission(request, null, false);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<Long> orgIds = (List<Long>) checkR.getData();
        ArrayList<Integer> integers = new ArrayList<>();
        integers.add(0);
        integers.add(1);
        List employeeInfo = exportDao.getExportInorgname(code, name, orgname, unitname, jobname, integers, isReinstate, orgIds, isEntryStatus);
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, employeeInfo, ExportInorgnameDto.class, true);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "员工政治面貌";
        outputFile2007(response, wb, excelName);

    }

    @GetMapping("/exportFamily")
    @ApiOperation(value = "员工家庭关系")
    @PreAuthorize("hasAuthority('emp:rydc:update')")
    public void exportFamily(HttpServletResponse response, HttpServletRequest request,
                             @RequestParam(value = "code", required = false) String code,
                             @RequestParam(value = "name", required = false) String name,
                             @RequestParam(value = "orgname", required = false) String orgname,
                             @RequestParam(value = "unitname", required = false) String unitname,
                             @RequestParam(value = "jobname", required = false) String jobname,
                             @RequestParam(value = "isReinstate", required = false) Integer isReinstate,
                             @RequestParam(value = "isEntryStatus", required = false) Integer isEntryStatus) throws Exception {
        //获取当前用户角色下公司Id
        R checkR = loginController.checkOrgPermission(request, null, false);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<Long> orgIds = (List<Long>) checkR.getData();
        ArrayList<Integer> integers = new ArrayList<>();
        integers.add(0);
        integers.add(1);
        List employeeInfo = exportDao.getExportFamily(code, name, orgname, unitname, jobname, integers, isReinstate, orgIds, isEntryStatus);
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, employeeInfo, ExportFamilyDto.class, true);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "员工家庭关系";
        outputFile2007(response, wb, excelName);

    }

    @GetMapping("/exportRelation")
    @ApiOperation(value = "员工内部关系")
    @PreAuthorize("hasAuthority('emp:rydc:update')")
    public void exportRelation(HttpServletResponse response, HttpServletRequest request,
                               @RequestParam(value = "code", required = false) String code,
                               @RequestParam(value = "name", required = false) String name,
                               @RequestParam(value = "orgname", required = false) String orgname,
                               @RequestParam(value = "unitname", required = false) String unitname,
                               @RequestParam(value = "jobname", required = false) String jobname,
                               @RequestParam(value = "isReinstate", required = false) Integer isReinstate,
                               @RequestParam(value = "isEntryStatus", required = false) Integer isEntryStatus) throws Exception {
        //获取当前用户角色下公司Id
        R checkR = loginController.checkOrgPermission(request, null, false);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<Long> orgIds = (List<Long>) checkR.getData();
        ArrayList<Integer> integers = new ArrayList<>();
        integers.add(0);
        integers.add(1);
        List employeeInfo = exportDao.getExportRelation(code, name, orgname, unitname, jobname, integers, isReinstate, orgIds, isEntryStatus);
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, employeeInfo, ExportRelation.class, true);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "员工内部关系";
        outputFile2007(response, wb, excelName);

    }

    @PostMapping("/exportSecurityRecord")
    @ApiOperation(value = "社保记录")
    @PreAuthorize("hasAuthority('emp:securityCost:update')")
    public void exportSecurityRecord(HttpServletResponse response, String time,
                                     Integer type, Integer isCheck, Integer isConfirm,
                                     String unitName, String[] recordIds, HttpServletRequest request) throws Exception {
        Long[] ids = new Long[recordIds.length];
        int i = 0;
        for (String recordId : recordIds) {
            ids[i] = Long.parseLong(recordId);
            i++;
        }
        //获取当前用户角色下合同主体Id
        R checkR = loginController.checkContractPermission(request);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<ContractSubGroupEntity> contractSubs = (List<ContractSubGroupEntity>) checkR.getData();
        ArrayList<Long> contractIds = new ArrayList<Long>();
        for (ContractSubGroupEntity item : contractSubs
        ) {
            contractIds.add(item.getId());
        }
        List securityRecord = exportDao.getSecurityRecord(time, type, isCheck, isConfirm, unitName, ids, contractIds);
        for (int j = 0; j < securityRecord.size(); j++) {
            Map map = (Map) securityRecord.get(j);
            if (!ObjectUtils.isEmpty(map.get("job_level_id"))) {
                map.put("joblevel", jobController.jobLevelMap.get(Long.parseLong(map.get("job_level_id").toString())));
            }
            if (!ObjectUtils.isEmpty(map.get("org_id"))) {
                map.put("orgname", jobController.orgMap.get(Long.parseLong(map.get("org_id").toString())));
            }
            if (!ObjectUtils.isEmpty(map.get("job_id"))) {
                map.put("jobname", jobController.jobMap.get(Long.parseLong(map.get("job_id").toString())));
            }

        }
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, securityRecord, ExportSecurityRecordDto.class, true);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "导出参保明细";
        outputFile2007(response, wb, excelName);
    }

    /**
     * 全部导出
     *
     * @param response
     * @param time
     * @param type
     * @param isCheck
     * @param isConfirm
     * @param request
     * @throws Exception
     */
    @GetMapping("/exportSecurityRecordAll")
    @ApiOperation(value = "导出全部-社保记录")
//    @PreAuthorize("hasAuthority('emp:securityCost:update')")
    public void exportSecurityRecordAll(HttpServletResponse response, String time,
                                        Integer type, Integer isCheck, Integer isConfirm,
                                        String unitName, HttpServletRequest request) throws Exception {
        //获取当前用户角色下合同主体Id
        R checkR = loginController.checkContractPermission(request);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<ContractSubGroupEntity> contractSubs = (List<ContractSubGroupEntity>) checkR.getData();
        ArrayList<Long> contractIds = new ArrayList<Long>();
        for (ContractSubGroupEntity item : contractSubs
        ) {
            contractIds.add(item.getId());
        }
        List securityRecord = exportDao.getSecurityRecordAll(time, type, isCheck, isConfirm, unitName, contractIds);
        for (int j = 0; j < securityRecord.size(); j++) {
            Map map = (Map) securityRecord.get(j);
            if (!ObjectUtils.isEmpty(map.get("job_level_id"))) {
                map.put("joblevel", jobController.jobLevelMap.get(Long.parseLong(map.get("job_level_id").toString())));
            }
            if (!ObjectUtils.isEmpty(map.get("org_id"))) {
                map.put("orgname", jobController.orgMap.get(Long.parseLong(map.get("org_id").toString())));
            }
            if (!ObjectUtils.isEmpty(map.get("job_id"))) {
                map.put("jobname", jobController.jobMap.get(Long.parseLong(map.get("job_id").toString())));
            }

        }
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, securityRecord, ExportSecurityRecordDto.class, true);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "导出参保明细";
        outputFile2007(response, wb, excelName);
    }

    @PostMapping("/exportFundsRecord")
    @ApiOperation(value = "公积金记录")
    @PreAuthorize("hasAuthority('emp:securityCost:update')")
    public void exportFundsRecord(HttpServletResponse response, String time,
                                  Integer type, Integer isCheck, Integer isConfirm,
                                  String unitName, String[] recordIds, HttpServletRequest request) throws Exception {
        Long[] ids = new Long[recordIds.length];
        int i = 0;
        for (String recordId : recordIds) {
            ids[i] = Long.parseLong(recordId);
            i++;
        }

        //获取当前用户角色下合同主体Id
        R checkR = loginController.checkContractPermission(request);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<ContractSubGroupEntity> contractSubs = (List<ContractSubGroupEntity>) checkR.getData();
        ArrayList<Long> contractIds = new ArrayList<Long>();
        for (ContractSubGroupEntity item : contractSubs
        ) {
            contractIds.add(item.getId());
        }
        List securityRecord = exportDao.getFundsRecord(time, type, isCheck, isConfirm, unitName, ids, contractIds);
        for (int j = 0; j < securityRecord.size(); j++) {
            Map map = (Map) securityRecord.get(j);
            if (!ObjectUtils.isEmpty(map.get("job_level_id"))) {
                map.put("joblevel", jobController.jobLevelMap.get(Long.parseLong(map.get("job_level_id").toString())));
            }
            if (!ObjectUtils.isEmpty(map.get("org_id"))) {
                map.put("orgname", jobController.orgMap.get(Long.parseLong(map.get("org_id").toString())));
            }
            if (!ObjectUtils.isEmpty(map.get("job_id"))) {
                map.put("jobname", jobController.jobMap.get(Long.parseLong(map.get("job_id").toString())));
            }
        }

        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, securityRecord, ExportFundsRecordDto.class, true);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "公积金记录";
        outputFile2007(response, wb, excelName);
    }

    @GetMapping("/exportFundsRecordAll")
    @ApiOperation(value = "导出全部-公积金记录")
//    @PreAuthorize("hasAuthority('emp:securityCost:update')")
    public void exportFundsRecordAll(HttpServletResponse response, String time,
                                     Integer type, Integer isCheck, Integer isConfirm,
                                     String unitName, HttpServletRequest request) throws Exception {

        //获取当前用户角色下合同主体Id
        R checkR = loginController.checkContractPermission(request);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<ContractSubGroupEntity> contractSubs = (List<ContractSubGroupEntity>) checkR.getData();
        ArrayList<Long> contractIds = new ArrayList<Long>();
        for (ContractSubGroupEntity item : contractSubs
        ) {
            contractIds.add(item.getId());
        }
        List securityRecord = exportDao.getFundsRecordAll(time, type, isCheck, isConfirm, unitName, contractIds);
        for (int j = 0; j < securityRecord.size(); j++) {
            Map map = (Map) securityRecord.get(j);
            if (!ObjectUtils.isEmpty(map.get("job_level_id"))) {
                map.put("joblevel", jobController.jobLevelMap.get(Long.parseLong(map.get("job_level_id").toString())));
            }
            if (!ObjectUtils.isEmpty(map.get("org_id"))) {
                map.put("orgname", jobController.orgMap.get(Long.parseLong(map.get("org_id").toString())));
            }
            if (!ObjectUtils.isEmpty(map.get("job_id"))) {
                map.put("jobname", jobController.jobMap.get(Long.parseLong(map.get("job_id").toString())));
            }
        }

        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, securityRecord, ExportFundsRecordDto.class, true);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "公积金记录";
        outputFile2007(response, wb, excelName);
    }

    @GetMapping("/exportUserBankCard")
    @ApiOperation(value = "导出工资卡")
    public void exportUserBankCard(HttpServletResponse response, @RequestParam(value = "name", required = false) String name,
                                   @RequestParam(value = "code", required = false) String code,
                                   @RequestParam(value = "orgname", required = false) String orgname,
                                   @RequestParam(value = "unitname", required = false) String unitname,
                                   @RequestParam(value = "jobname", required = false) String jobname,
                                   @RequestParam(value = "status", required = false) Integer status, HttpServletRequest request) throws Exception {
        //获取当前用户角色下公司Id
        R checkR = loginController.checkOrgPermission(request, null, false);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<Long> orgIds = (List<Long>) checkR.getData();

        List<UserBankCardExportDto> userBankCardRecord = exportDao.getUserBankCardPage(orgIds, name, code, orgname, unitname, jobname, status);
        for (UserBankCardExportDto userBankCardExportDto : userBankCardRecord) {
            if (userBankCardExportDto.getOrgId() == null) {
                //如果本地库没有，则去sqlServer查询
                EmployeeInfoEntity employeeInfoEntity = baseMssqlService.finEmpFromMssqlByCode(code);
                // employeeModelDto = findEmpByIdByCode(code);
                if (!ObjectUtils.isEmpty(employeeInfoEntity)) {
                    userBankCardExportDto.setOrgname(employeeInfoEntity.getOrgname());
                    userBankCardExportDto.setUnitname(employeeInfoEntity.getUnitname());
                    userBankCardExportDto.setJobLevel(employeeInfoEntity.getJobLevel());
                    userBankCardExportDto.setJobname(employeeInfoEntity.getJobname());
                }
            } else {
                if (userBankCardExportDto.getJobId() != null)
                    userBankCardExportDto.setJobname(jobController.getJobName(userBankCardExportDto.getJobId()));
                if (userBankCardExportDto.getOrgId() != null)
                    userBankCardExportDto.setOrgname(jobController.getOrgNameById(userBankCardExportDto.getOrgId()));
                if (userBankCardExportDto.getUnitId() != null)
                    userBankCardExportDto.setUnitname(jobController.getOrgNameById(userBankCardExportDto.getUnitId()));
            }

            userBankCardExportDto.setEffectiveDateStr(DateUtils.format(userBankCardExportDto.getEffectiveDate()));
        }
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, userBankCardRecord, UserBankCardExportDto.class, false);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "员工工资卡";
        outputFile2007(response, wb, excelName);
    }



    @GetMapping("/exportEmployeeRecruitment")
    @ApiOperation(value = "导出新员工试用期管理和新员工转正跟踪")
    public void exportEmployeeRecruitment(HttpServletResponse response,@RequestBody RegularVo regularVo, HttpServletRequest request) throws Exception{

        List<Long>  jobSystemIds = new ArrayList<>();
        List<Long>  jlineSystemIds = new ArrayList<>();
        //获取当前用户角色下公司Id
        R checkR = loginController.checkOrgPermission(request, null, false);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<Long> orgIds = (List<Long>) checkR.getData();
//        log.info("公司组orgIds：{}",orgIds);
        //体系
        if(!StringUtil.isNullOrEmpty(regularVo.getJobSystem())){
            jobSystemIds = baseMssqlService.findSystemIdByName(regularVo.getJobSystem());
        }
        //体系
        if(!StringUtil.isNullOrEmpty(regularVo.getLineSystem())){
            jlineSystemIds = baseMssqlService.findLineIdByName(regularVo.getLineSystem());
        }
        List<Long> org = null;
        List<Long> unit = null;
        List<Long> job = null;
        if(!ObjectUtils.isEmpty(regularVo.getOrgname())){
            org = baseMssqlService.finOrgByOrgName(regularVo.getOrgname());
        }
        if(!ObjectUtils.isEmpty(regularVo.getUnitname())){
            unit = baseMssqlService.finOrgByOrgName(regularVo.getUnitname());
        }

        if(!ObjectUtils.isEmpty(regularVo.getJobname())){
            job = baseMssqlService.findJobIdByJobName(regularVo.getJobname());
        }
        JSONObject  userInfo = loginController.getUserInfo(request).getData();
        String code = userInfo.getString("usercode");
        regularVo.setCode(code);
        List<EmployeeRecruitmentEntity> employeeRecruitmentEntityList= employeeRecruitmentService.getRegularList(regularVo,jobSystemIds,jlineSystemIds,orgIds,org,unit,job);

        String excelName = "新员工试用期转正";
        XSSFWorkbook wb=null;
        if(regularVo.getType().equals(1)){
            Map<Integer,String> map=new HashMap<>();
            map.put(0,"未转正");
            map.put(1,"转正");
            map.put(2,"延期转正");
            map.put(3,"不予转正");
            map.put(4,"提前转正");
            List<EmployeeRecruitmentExportDto> employeeRecruitmentExportDtoList=new ArrayList<>();
            for (EmployeeRecruitmentEntity employeeRecruitmentEntity : employeeRecruitmentEntityList) {

                EmployeeRecruitmentExportDto employeeRecruitmentExportDto=BeanUtilsMabach.doToDto(employeeRecruitmentEntity, EmployeeRecruitmentExportDto.class);
                if (employeeRecruitmentExportDto.getJobId() != null) {
                    employeeRecruitmentExportDto.setJobname(jobController.getJobName(employeeRecruitmentExportDto.getJobId()));
                }
                if (employeeRecruitmentExportDto.getOrgId() != null) {
                    employeeRecruitmentExportDto.setOrgname(jobController.getOrgNameById(employeeRecruitmentExportDto.getOrgId()));
                }
                if (employeeRecruitmentExportDto.getUnitId() != null) {
                    employeeRecruitmentExportDto.setUnitname(jobController.getOrgNameById(employeeRecruitmentExportDto.getUnitId()));
                }
                employeeRecruitmentExportDto.setJobLevel(jobController.jobLevelMap.get(employeeRecruitmentExportDto.getJobLevelId()));
                employeeRecruitmentExportDto.setIsRegularName(map.get(employeeRecruitmentExportDto.getIsRegular()));
                employeeRecruitmentExportDto.setHiredateStr(DateUtils.format(employeeRecruitmentEntity.getHiredate()));
                employeeRecruitmentExportDto.setRegularDayStr(DateUtils.format(employeeRecruitmentEntity.getRegularDay()));

            }
             wb = ExcelUtil.exportExcel3(null, employeeRecruitmentExportDtoList, EmployeeRecruitmentExportDto.class, false);

        }else{
            List<EmployeeRecruitmentTurnExportDto> employeeRecruitmentTurnExportDtoList=new ArrayList<>();
            Map<Integer,String> map=new HashMap<>();
            map.put(0,"未提醒");
            map.put(1,"已提醒");

            Map<Integer,String> statusMap=new HashMap<>();
            statusMap.put(0,"待发起");
            statusMap.put(1,"已发起");
            statusMap.put(2,"已审批");

            for (EmployeeRecruitmentEntity employeeRecruitmentEntity : employeeRecruitmentEntityList) {

                EmployeeRecruitmentTurnExportDto employeeRecruitmentTurnExportDto=BeanUtilsMabach.doToDto(employeeRecruitmentEntity, EmployeeRecruitmentTurnExportDto.class);
                if (employeeRecruitmentTurnExportDto.getJobId() != null) {
                    employeeRecruitmentTurnExportDto.setJobname(jobController.getJobName(employeeRecruitmentTurnExportDto.getJobId()));
                }
                if (employeeRecruitmentTurnExportDto.getOrgId() != null) {
                    employeeRecruitmentTurnExportDto.setOrgname(jobController.getOrgNameById(employeeRecruitmentTurnExportDto.getOrgId()));
                }
                if (employeeRecruitmentTurnExportDto.getUnitId() != null) {
                    employeeRecruitmentTurnExportDto.setUnitname(jobController.getOrgNameById(employeeRecruitmentTurnExportDto.getUnitId()));
                }
                employeeRecruitmentTurnExportDto.setJobLevel(jobController.jobLevelMap.get(employeeRecruitmentTurnExportDto.getJobLevelId()));
                employeeRecruitmentTurnExportDto.setIsRegularRemindStr(map.get(employeeRecruitmentEntity.getIsRegularRemind()));
                employeeRecruitmentTurnExportDto.setStatusStr(map.get(employeeRecruitmentEntity.getRegularBpmStatus()));
                employeeRecruitmentTurnExportDto.setHiredateStr(DateUtils.format(employeeRecruitmentEntity.getHiredate()));
                employeeRecruitmentTurnExportDto.setRegularDayStr(DateUtils.format(employeeRecruitmentEntity.getRegularDay()));
            }
             wb = ExcelUtil.exportExcel3(null, employeeRecruitmentTurnExportDtoList, EmployeeRecruitmentTurnExportDto.class, false);
            excelName = "新员工转正跟踪";
        }

        response.setContentType("application/vnd.ms-excel;charset=utf-8");

        outputFile2007(response, wb, excelName);
    }




    @GetMapping("/exportRegularBpm")
    @ApiOperation(value = "导出干部考察期管理和干部转正跟踪")
    public void exportRegularBpm(HttpServletResponse response,@RequestBody RegularListVo regularListVo, HttpServletRequest request) throws Exception{

        List<Long>  jobSystemIds = new ArrayList<>();
        List<Long>  jlineSystemIds = new ArrayList<>();
        //获取当前用户角色下公司Id
        R checkR = loginController.checkOrgPermission(request, null, false);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<Long> orgIds = (List<Long>) checkR.getData();
        List<Long> orgId = null;
        List<Long> unitId = null;
        List<Long> jobId = null;
        if(!ObjectUtils.isEmpty(regularListVo.getOrgname())){
            orgId = baseMssqlService.finOrgByOrgName(regularListVo.getOrgname());
        }
        if(!ObjectUtils.isEmpty(regularListVo.getBeforeUnitName())){
            unitId = baseMssqlService.finOrgByOrgName(regularListVo.getBeforeUnitName());
        }
        if(!ObjectUtils.isEmpty(regularListVo.getBeforeJobName())){
            jobId = baseMssqlService.findJobIdByJobName(regularListVo.getBeforeJobName());
        }

        PageResult<RegularBpmEntity> result = regularBpmService.getRegularList(regularListVo.getType(),regularListVo.getStartTime(),regularListVo.getName(),
                regularListVo.getCode(),regularListVo.getEndTime(),regularListVo.getOid(),false,orgIds,regularListVo.getRegularBpmStatus(),
                orgId,unitId,jobId,regularListVo.getPageNum(),9999999);
        List<RegularBpmEntity> regularBpmEntityList=result.getList();
        String excelName = "干部考察期管理";
        XSSFWorkbook wb=null;
        Map<Integer,String> regularTypeMap=new HashMap<>();
        regularTypeMap.put(1,"专业职级晋升");
        regularTypeMap.put(2,"管理职级晋升");
        regularTypeMap.put(3,"调动管理晋升");

        Map<Integer,String> isRegularRemindMap=new HashMap<>();
        isRegularRemindMap.put(0,"未提醒");
        isRegularRemindMap.put(1,"已提醒");

        Map<Integer,String> isRegularMap=new HashMap<>();
        isRegularMap.put(0,"未转正");
        isRegularMap.put(1,"转正");
        isRegularMap.put(2,"延期转正");
        isRegularMap.put(3,"不予转正");
        isRegularMap.put(4,"提前转正");
        isRegularMap.put(5,"离职终止");

        Map<Integer,String> statusMap=new HashMap<>();
        statusMap.put(0,"待发起");
        statusMap.put(1,"已发起");
        statusMap.put(2,"已审批");

        Map<Integer,String> map=new HashMap<>();
        map.put(0,"未发送");
        map.put(1,"已发送");

        List<CadreRegulartExportDto> cadreRegulartExportDtoList=new ArrayList<>();
        List<CadreRegulartTrackExportDto> cadreRegulartTrackExportDtoList=new ArrayList<>();
        for (RegularBpmEntity regularBpmEntity : regularBpmEntityList) {

            RegulartExportDto regulartExportDto=BeanUtilsMabach.doToDto(regularBpmEntity, RegulartExportDto.class);
            if (regularBpmEntity.getOrgId() != null) {
                regulartExportDto.setOrgname(jobController.getOrgNameById(regularBpmEntity.getOrgId()));
            }
            if (regularBpmEntity.getBeforeUnitId() != null) {
                regulartExportDto.setBeforeUnitName(jobController.getOrgNameById(regularBpmEntity.getBeforeUnitId()));
            }
            if (regularBpmEntity.getBeforeJobId() != null) {
                regulartExportDto.setBeforeJobName(jobController.getJobName(regularBpmEntity.getBeforeJobId()));
            }
            regulartExportDto.setBeforeJobLevel(jobController.jobLevelMap.get(regularBpmEntity.getBeforeJobLevelId()));
            if (regularBpmEntity.getUnitId() != null) {
                regulartExportDto.setUnitname(jobController.getOrgNameById(regularBpmEntity.getUnitId()));
            }
            if (regularBpmEntity.getJobId() != null) {
                regulartExportDto.setJobname(jobController.getJobName(regularBpmEntity.getJobId()));
            }
            regulartExportDto.setJobLevel(jobController.jobLevelMap.get(regularBpmEntity.getJobLevelId()));
            regulartExportDto.setCreateTimeStr(DateUtils.format(regularBpmEntity.getCreateTime()));
            regulartExportDto.setRegularTypeStr(regularTypeMap.get(regularBpmEntity.getRegularType()));
            regulartExportDto.setApplyTimeStr(DateUtils.format(regularBpmEntity.getApplyTime()));
            regulartExportDto.setRegularDayStr(DateUtils.format(regularBpmEntity.getRegularDay()));
            regulartExportDto.setCountdown(DateUtils.daysBetween(regularBpmEntity.getRegularDay(),regularBpmEntity.getApplyTime())+"");
            if(regularListVo.getBusinessType().equals(1)) {
                CadreRegulartExportDto cadreRegulartExportDto=BeanUtilsMabach.doToDto(regulartExportDto, CadreRegulartExportDto.class);
                cadreRegulartExportDto.setIsRegularRemindStr(isRegularRemindMap.get(regularBpmEntity.getIsRegularRemind()));
                cadreRegulartExportDto.setIsRegularStr(isRegularMap.get(regularBpmEntity.getIsRegular()));
                cadreRegulartExportDtoList.add(cadreRegulartExportDto);
            }else{
                CadreRegulartTrackExportDto cadreRegulartTrackExportDto=BeanUtilsMabach.doToDto(regulartExportDto, CadreRegulartTrackExportDto.class);
                cadreRegulartTrackExportDto.setIsRegularRemindStr(map.get(regularBpmEntity.getIsRegularRemind()));
                cadreRegulartTrackExportDto.setRegularBpmStatusStr(statusMap.get(regularBpmEntity.getRegularBpmStatus()));
                cadreRegulartTrackExportDtoList.add(cadreRegulartTrackExportDto);
            }
        }
        if(regularListVo.getBusinessType().equals(1)) {
            wb = ExcelUtil.exportExcel3(null, cadreRegulartExportDtoList, CadreRegulartExportDto.class, false);
        }else {
            wb = ExcelUtil.exportExcel3(null, cadreRegulartTrackExportDtoList, EmployeeRecruitmentTurnExportDto.class, false);
            excelName = "干部转正跟踪";
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");

        outputFile2007(response, wb, excelName);
    }





    @GetMapping("/exportMemberChange")
    @ApiOperation(value = "导出员工社保公积金变更")
    public void exportMemberChange(HttpServletResponse response, @RequestParam(value = "securityId", required = false) String securityId,
                                   @RequestParam(value = "date", required = false) String date) throws Exception {
        String startTime = date + "-01 00:00:00";
        String endTime = date + "-31 23:59:59";
        List<SecurityMemberChangeListDto> list = securityChangeDao.getMemberChangeList(securityId, startTime, endTime);
        Map<String, String> map = new HashMap<>();
        map.put("0", "增员");
        map.put("1", "减员");
        map.put("2", "正常");
        map.put("3", "变更");
        map.put("4", "更新");
        Integer i = 1;
        for (SecurityMemberChangeListDto changeListDto : list) {
            changeListDto.setNo(i.toString());
            changeListDto.setNationName(jobController.nationality.get(changeListDto.getNationality()));
            changeListDto.setTypeName(map.get(changeListDto.getType()));
            i++;
        }
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, list, SecurityMemberChangeListDto.class, false);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "员工社保公积金变更";
        outputFile2007(response, wb, excelName);
    }

    @GetMapping("/exportContractRecord")
    @ApiOperation(value = "导出员工员工合同台账")
    public void exportContractRecord(HttpServletRequest request,
                                     @RequestParam(value = "code", required = false) String code,
                                     @RequestParam(value = "name", required = false) String name,
                                     @RequestParam(value = "orgName", required = false) String orgName,
                                     @RequestParam(value = "unitName", required = false) String unitName,
                                     @RequestParam(value = "contractType", required = false) Integer contractType,
                                     @RequestParam(value = "contractTimeType", required = false) Integer contractTimeType,
                                     @RequestParam(value = "contractEndDate", required = false) String contractEndDate,
                                     @RequestParam(value = "status", required = false) Integer status,
                                     HttpServletResponse response) throws Exception {
        R checkR = loginController.checkOrgPermission(request, null, false);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<Long> orgIds = (List<Long>) checkR.getData();
        List<Long> unitId = new ArrayList<>();
        List<Long> orgId = new ArrayList<>();
        if (!StringUtils.isEmpty(orgName)) {
            orgId = baseMssqlService.finOrgByOrgName(orgName);
        }
        if (!StringUtils.isEmpty(unitName)) {
            unitId = baseMssqlService.finOrgByOrgName(unitName);
        }
        List<ContractRecordDto> list = exportDao.getRecordList(code, name, orgId, unitId, contractType,
                contractTimeType, status, contractEndDate, orgIds);
        Map<Integer, String> contractTypeMap = new HashMap<>();
        contractTypeMap.put(1, "劳动合同");
        contractTypeMap.put(2, "退休返聘协议");
        contractTypeMap.put(3, "实习协议");
        contractTypeMap.put(4, "解除协议");
        Map<Integer, String> contractTimeTypeMap = new HashMap<>();
        contractTimeTypeMap.put(0, "半年");
        contractTimeTypeMap.put(1, "一年");
        contractTimeTypeMap.put(2, "二年");
        contractTimeTypeMap.put(3, "三年");
        Map<Integer, String> operateTypeMap = new HashMap<>();
        operateTypeMap.put(1, "入职");
        operateTypeMap.put(2, "调动");
        operateTypeMap.put(3, "晋升");
        operateTypeMap.put(4, "降级");
        operateTypeMap.put(5, "离职");
        operateTypeMap.put(6, "续签");
        Map<Integer, String> endMap = new HashMap<>();
        endMap.put(1, "合同解除");
        endMap.put(2, "终止解除");
        Map<String, String> employeeStatus = new HashMap<>();
        employeeStatus.put("0", "未入职");
        employeeStatus.put("2", "正式");
        employeeStatus.put("3", "离职");
        employeeStatus.put("4", "退休");
        employeeStatus.put("5", "内退");
        employeeStatus.put("6", "离休");
        employeeStatus.put("7", "待岗");
        employeeStatus.put("9", "实习");
        employeeStatus.put("10", "实习结束");
        employeeStatus.put("11", "调动中");
        List<ExportContractDto> recordList = new ArrayList<>();
        for (ContractRecordDto dto : list) {
            ExportContractDto contractDto = BeanUtilsMabach.doToDto(dto, ExportContractDto.class);
            contractDto.setJobName(jobController.jobMap.get(dto.getJobId()));
            contractDto.setOrgName(jobController.orgMap.get(dto.getOrgId()));
            contractDto.setUnitName(jobController.orgMap.get(dto.getUnitId()));
            contractDto.setBeginContractTypeName(contractTypeMap.get(contractDto.getBeginContractType()));
            contractDto.setContractTimeTypeName(contractTimeTypeMap.get(contractDto.getContractTimeType()));
            contractDto.setHiredateStr(DateUtils.format(contractDto.getHiredate()));
            contractDto.setContractBeginDateStr(DateUtils.format(contractDto.getContractBeginDate()));
            contractDto.setContractEndStr(DateUtils.format(contractDto.getContractEndDate()));
            contractDto.setEndTypeName(endMap.get(contractDto.getEndType()));
            contractDto.setOperateTypeName(StringUtils.isEmpty(contractDto.getOperateType()) ? "" : operateTypeMap.get(Integer.parseInt(contractDto.getOperateType())));
            contractDto.setEmployeestatus(employeeStatus.get(dto.getEmployeestatus()));
            recordList.add(contractDto);
        }
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, recordList, ExportContractDto.class, false);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "员工合同台账";
        outputFile2007(response, wb, excelName);
    }

    @GetMapping("/exportInsurance")
    @ApiOperation(value = "导出员工雇主险台账")
    public void exportInsurance(HttpServletResponse response, HttpServletRequest request) throws Exception {
        R checkR = loginController.checkOrgPermission(request, null, false);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<Long> orgIds = (List<Long>) checkR.getData();
        Map<String, String> jobType = new HashMap<>();
        jobType.put("0", "全日制用工");
        jobType.put("1", "非全日制用工");
        jobType.put("2", "劳务派遣用工");
        jobType.put("3", "退休返聘");
        jobType.put("4", "管培生");
        jobType.put("5", "实习生");
        jobType.put("3-6", "实习生");
        Map<String, String> employeeStatus = new HashMap<>();
        employeeStatus.put("0", "未入职");
        employeeStatus.put("2", "正式");
        employeeStatus.put("3", "离职");
        employeeStatus.put("4", "退休");
        employeeStatus.put("5", "内退");
        employeeStatus.put("6", "离休");
        employeeStatus.put("7", "待岗");
        employeeStatus.put("9", "实习");
        employeeStatus.put("10", "实习结束");
        employeeStatus.put("11", "调动中");
        List<InsuranceMemberRecordDto> recordDtoList = exportDao.getMemberRecordList(orgIds);
        for (InsuranceMemberRecordDto dto : recordDtoList) {
            dto.setJobLevel(jobController.jobLevelMap.get(dto.getJobLevelId()));
            dto.setUnitname(jobController.orgMap.get(dto.getUnitId()));
            dto.setJobname(jobController.jobMap.get(dto.getJobId()));
            dto.setOrgname(jobController.orgMap.get(dto.getOrgId()));
            dto.setGenderName(new Integer(1).equals(dto.getGender()) ? "男" : "女");
            dto.setJobType(jobType.get(dto.getJobType()));
            dto.setEmployeestatus(employeeStatus.get(dto.getEmployeestatus()));
            dto.setStatusName(new Integer(1).equals(dto.getStatus()) ? "生效" : "失效");
            dto.setTypeName(new Integer(1).equals(dto.getType()) ? "参与" : "未参与");
            dto.setHiredateStr(DateUtils.format(dto.getHiredate()));
            dto.setOperateTimeStr(DateUtils.format(dto.getOperateTime(), "yyyy-MM-dd HH:mm:ss"));
        }
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, recordDtoList, InsuranceMemberRecordDto.class, false);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "雇主险台账";
        outputFile2007(response, wb, excelName);
    }

    @GetMapping("/exportInsuranceChange")
    @ApiOperation(value = "导出员工雇主险变更台账")
    public void exportInsuranceChange(@RequestParam(value = "startTime", required = false)String startTime,
                                      @RequestParam(value = "endTime", required = false)String endTime,
                                      @RequestParam(value = "employeestatus", required = false)String employeestatus,
                                      @RequestParam(value = "orgname", required = false)String orgname,
                                      @RequestParam(value = "companyName", required = false)String companyName,
                                      @RequestParam(value = "securityType", required = false)Integer securityType,
                                      @RequestParam(value = "type", required = false)Integer type,
                                      @RequestParam(value = "operateName", required = false)String operateName,
                                      HttpServletResponse response, HttpServletRequest request) throws Exception {
        R checkR = loginController.checkOrgPermission(request, null, false);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<Long> orgId = null;
        if (!StringUtils.isEmpty(orgname)) {
            orgId = baseMssqlService.finOrgByOrgName(orgname);
        }
        List<Long> orgIds = (List<Long>) checkR.getData();
        Map<String, String> jobType = new HashMap<>();
        jobType.put("0", "全日制用工");
        jobType.put("1", "非全日制用工");
        jobType.put("2", "劳务派遣用工");
        jobType.put("3", "退休返聘");
        jobType.put("4", "管培生");
        jobType.put("5", "实习生");
        jobType.put("3-6", "实习生");
        Map<String, String> employeeStatus = new HashMap<>();
        employeeStatus.put("0", "未入职");
        employeeStatus.put("2", "正式");
        employeeStatus.put("3", "离职");
        employeeStatus.put("4", "退休");
        employeeStatus.put("5", "内退");
        employeeStatus.put("6", "离休");
        employeeStatus.put("7", "待岗");
        employeeStatus.put("9", "实习");
        employeeStatus.put("10", "实习结束");
        employeeStatus.put("11", "调动中");
        Map<String, String> typeStatus = new HashMap<>();
        typeStatus.put("0", "增员");
        typeStatus.put("1", "减员");
        typeStatus.put("3", "补缴");
        typeStatus.put("4", "基数变更");
        List<InsuranceChangeDto> recordDtoList = exportDao.getMemberChangeList(orgIds, startTime, endTime,
                companyName, employeestatus, type, securityType, operateName, orgId);
        for (InsuranceChangeDto dto : recordDtoList) {
            dto.setJobLevel(jobController.jobLevelMap.get(dto.getJobLevelId()));
            dto.setUnitName(jobController.orgMap.get(dto.getUnitId()));
            dto.setJobName(jobController.jobMap.get(dto.getJobId()));
            dto.setOrgName(jobController.orgMap.get(dto.getOrgId()));
            dto.setGenderName(new Integer(1).equals(dto.getGender()) ? "男" : "女");
            dto.setJobType(jobType.get(dto.getJobType()));
            dto.setEmployeestatus(employeeStatus.get(dto.getEmployeestatus()));
            dto.setStatusName(new Integer(1).equals(dto.getStatus()) ? "生效" : "失效");
            dto.setTypeName(typeStatus.get(dto.getType().toString()));
            dto.setHiredateStr(DateUtils.format(dto.getHiredate()));
            dto.setOperateTimeStr(DateUtils.format(dto.getOperationTime(), "yyyy-MM-dd HH:mm:ss"));
        }
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, recordDtoList, InsuranceMemberRecordDto.class, false);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "雇主险变更记录";
        outputFile2007(response, wb, excelName);
    }


    private void outputFile(HttpServletResponse response, HSSFWorkbook wb, String excelName) throws Exception {
        //转码防止乱码
        response.addHeader("Content-Disposition", "attachment;filename=" + new String(excelName.getBytes("gb2312"), "ISO8859-1") + ".xls");
        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.close();
    }

    private void outputFile2007(HttpServletResponse response, XSSFWorkbook wb, String excelName) throws Exception {
        //转码防止乱码
        response.addHeader("Content-Disposition", "attachment;filename=" + new String(excelName.getBytes("gb2312"), "ISO8859-1") + ".xlsx");
        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.close();
    }

    @GetMapping("/exportSecurityRecordReport")
    @ApiOperation("导出-社保报表")
    public void exportSecurityRecordReportList(HttpServletResponse response, @RequestParam(value = "operateTime", required = false) String operateTime, @RequestParam(value = "name", required = false) String name,
                                               @RequestParam(value = "unitName", required = false) String unitName, @RequestParam(value = "isAllYear", required = false) Integer isAllYear, HttpServletRequest request) throws Exception {
        String time = "";
        String endTime = "";
//        //获取当前用户角色下公司Id
//        R checkR = loginController.checkOrgPermission(request, null, false);
//        if (!R.isOk.equals(checkR.getStatus())) {
//            return;
//        }
//        List<Long> orgIds = (List<Long>) checkR.getData();
        //获取当前用户角色下合同主体Id
        R checkR = loginController.checkContractPermission(request);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<ContractSubGroupEntity> contractSubs = (List<ContractSubGroupEntity>) checkR.getData();
        ArrayList<Long> contractIds = new ArrayList<Long>();
        for (ContractSubGroupEntity item : contractSubs
        ) {
            contractIds.add(item.getId());
        }

        //判断是按年还是按月结算
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");//注意月份是MM
        Date date = null;
        if (operateTime != null) {
            date = simpleDateFormat.parse(operateTime);
        }

        if (!ObjectUtils.isEmpty(operateTime) && isAllYear.equals(1)) {
            time = DateUtils.format(date, "yyyy-MM") + "-01 00:00:00";
            endTime = DateUtils.format(date, "yyyy-MM") + "-31 23:59:59";
        } else if (!ObjectUtils.isEmpty(date) && isAllYear.equals(0)) {
            time = DateUtils.format(date, "yyyy") + "-01 00:00:00";
            endTime = DateUtils.format(date, "yyyy") + "-12-31 23:59:59";
        }

        if (name != null && name.equals("null")) {
            name = null;
        }
        if (unitName != null && unitName.equals("null")) {
            unitName = null;
        }
        List<SecurityRecordReportDto> exportRecordReport = securityRecordDao.getExportRecordReport(time, endTime,
                1,//已确认
                name,
                unitName, contractIds);

        if (!CollectionUtils.isEmpty(exportRecordReport)) {
            for (SecurityRecordReportDto item : exportRecordReport) {
                if (item.getJobSystemId() != null) {
                    item.setJobSystem(jobController.systemMap.get(item.getJobSystemId()));
                }
                if (item.getOrgId() != null) {
                    item.setOrgName(jobController.orgMap.get(item.getOrgId()));
                }
                if (item.getUnitId() != null) {
                    item.setDepartMentName(jobController.orgMap.get(item.getUnitId()));
                }
                if (item.getCreateTime() != null) {
                    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                    item.setCanbaoTime(formatter.format(item.getCreateTime()));
                }
            }
        }
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, exportRecordReport, SecurityRecordReportDto.class, false);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "社保明细查询报表";
        outputFile2007(response, wb, excelName);
    }

    @GetMapping("/exportFundRecordReport")
    @ApiOperation("导出-公积金报表")
    public void exportFundRecordReportList(HttpServletResponse response, @RequestParam(value = "operateTime", required = false) String operateTime, @RequestParam(value = "name", required = false) String name,
                                           @RequestParam(value = "unitName", required = false) String unitName, @RequestParam(value = "isAllYear", required = false) String isAllYear, HttpServletRequest request) throws Exception {
        String time = "";
        String endTime = "";
        //获取当前用户角色下公司Id
//        R checkR = loginController.checkOrgPermission(request, null, false);
//        if (!R.isOk.equals(checkR.getStatus())) {
//            return;
//        }
//        List<Long> orgIds = (List<Long>) checkR.getData();
        //获取当前用户角色下合同主体Id
        R checkR = loginController.checkContractPermission(request);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<ContractSubGroupEntity> contractSubs = (List<ContractSubGroupEntity>) checkR.getData();
        ArrayList<Long> contractIds = new ArrayList<Long>();
        for (ContractSubGroupEntity item : contractSubs
        ) {
            contractIds.add(item.getId());
        }
        //判断是按年还是按月结算
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");//注意月份是MM
        Date date = null;
        if (operateTime != null) {
            date = simpleDateFormat.parse(operateTime);
        }

        if (!ObjectUtils.isEmpty(operateTime) && isAllYear.equals("1")) {
            time = DateUtils.format(date, "yyyy-MM") + "-01 00:00:00";
            endTime = DateUtils.format(date, "yyyy-MM") + "-31 23:59:59";
        } else if (!ObjectUtils.isEmpty(date) && isAllYear.equals("0")) {
            time = DateUtils.format(date, "yyyy") + "-01 00:00:00";
            endTime = DateUtils.format(date, "yyyy") + "-12-31 23:59:59";
        }
        if (name != null && name.equals("null")) {
            name = null;
        }
        if (unitName != null && unitName.equals("null")) {
            unitName = null;
        }
        List<FundsRecordReportDto> exportRecordReport = fundsRecordDao.getExportRecordReport(time, endTime,
                1,//已确认
                name,
                unitName, contractIds);

        if (!CollectionUtils.isEmpty(exportRecordReport)) {
            for (FundsRecordReportDto item : exportRecordReport
            ) {
                if (item.getJobSystemId() != null) {
                    item.setJobSystem(jobController.systemMap.get(item.getJobSystemId()));
                }
                if (item.getOrgId() != null) {
                    item.setOrgName(jobController.orgMap.get(item.getOrgId()));
                }
                if (item.getUnitId() != null) {
                    item.setDepartMentName(jobController.orgMap.get(item.getUnitId()));
                }
                if (item.getCreateTime() != null) {
                    Date currentTime = new Date();
                    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                    item.setCreateUiTime(formatter.format(currentTime));
                }


            }

        }
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, exportRecordReport, FundsRecordReportDto.class, false);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "公积金明细查询报表";
        outputFile2007(response, wb, excelName);
    }


    // 带权限社保公积金方案查询
    @GetMapping("/ExportSecurityList")
    @ApiOperation("导出-社保方案报表")
    @PreAuthorize("hasAuthority('emp:security:update')")
    public void exportSecurityList(HttpServletResponse response, @RequestParam(value = "cityName", required = false) String cityName, @RequestParam(value = "unitName", required = false) String unitName, HttpServletRequest request) throws Exception {
        //验证是否有用户权限
        //获取当前用户角色下合同主体Id
        R checkR = loginController.checkContractPermission(request);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<ContractSubGroupEntity> contractSubs = (List<ContractSubGroupEntity>) checkR.getData();
        ArrayList<Long> contractIds = new ArrayList<Long>();
        for (ContractSubGroupEntity item : contractSubs
        ) {
            contractIds.add(item.getId());
        }


        String date = DateUtils.format(new Date(), "yyyy-MM");
        List<SecurityDto> listExcel = securityDao.getSecurityListExcel(contractIds, cityName, unitName, date + "-01 00:00:00", date + "-31 23:59:59");
        if (!CollectionUtils.isEmpty(listExcel)) {
            for (SecurityDto item : listExcel
            ) {
                item.setDateQuTime(date);
                item.setCount(item.getNumber().toString());
            }
        }
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, listExcel, SecurityDto.class, false);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "社保方案报表";
        outputFile2007(response, wb, excelName);
    }

    @GetMapping("/ExportFundList")
    @ApiOperation("导出-公积金方案报表")
    public void exportFundList(HttpServletResponse response, @RequestParam(value = "cityName", required = false) String cityName, @RequestParam(value = "unitName", required = false) String unitName, HttpServletRequest request) throws Exception {
        //验证是否有用户权限
        //获取当前用户角色下合同主体Id
        R checkR = loginController.checkContractPermission(request);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<ContractSubGroupEntity> contractSubs = (List<ContractSubGroupEntity>) checkR.getData();
        ArrayList<Long> contractIds = new ArrayList<Long>();
        for (ContractSubGroupEntity item : contractSubs
        ) {
            contractIds.add(item.getId());
        }


        String date = DateUtils.format(new Date(), "yyyy-MM");
        List<SecurityDto> listExcel = securityDao.getFundListExcel(contractIds, cityName, unitName, date + "-01 00:00:00", date + "-31 23:59:59");
        if (!CollectionUtils.isEmpty(listExcel)) {
            for (SecurityDto item : listExcel
            ) {
                item.setDateQuTime(date);
                item.setCount(item.getNumber().toString());
            }
        }
        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, listExcel, SecurityDto.class, false);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "公积金方案报表";
        outputFile2007(response, wb, excelName);
    }


    @PostMapping("/exportRegular")
    @ApiOperation(value = "导出查询转正信息")
    public void getRegularList(HttpServletResponse response, @RequestBody RegularVo regularVo, HttpServletRequest request) throws Exception {

        List<Long> jobSystemIds = new ArrayList<>();
        List<Long> jlineSystemIds = new ArrayList<>();
        //获取当前用户角色下公司Id
        R checkR = loginController.checkOrgPermission(request, null, false);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        List<Long> orgIds = (List<Long>) checkR.getData();
//        log.info("公司组orgIds：{}",orgIds);
        //体系
        if (!StringUtil.isNullOrEmpty(regularVo.getJobSystem())) {
            jobSystemIds = baseMssqlService.findSystemIdByName(regularVo.getJobSystem());
        }
        //体系
        if (!StringUtil.isNullOrEmpty(regularVo.getLineSystem())) {
            jlineSystemIds = baseMssqlService.findLineIdByName(regularVo.getLineSystem());
        }
        List<Long> org = null;
        List<Long> unit = null;
        List<Long> job = null;
        if (!ObjectUtils.isEmpty(regularVo.getOrgname())) {
            org = baseMssqlService.finOrgByOrgName(regularVo.getOrgname());
        }
        if (!ObjectUtils.isEmpty(regularVo.getUnitname())) {
            unit = baseMssqlService.finOrgByOrgName(regularVo.getUnitname());
        }

        if (!ObjectUtils.isEmpty(regularVo.getJobname())) {
            job = baseMssqlService.findJobIdByJobName(regularVo.getJobname());
        }
        JSONObject userInfo = loginController.getUserInfo(request).getData();
        String code = userInfo.getString("usercode");
        regularVo.setCode(code);
        List<EmployeeInfoEntity> resultList = employeeInfoService.getRegularListReport(regularVo, jobSystemIds, jlineSystemIds, orgIds, org, unit, job);
        ArrayList<RegularDto> dtos = new ArrayList<>();
        for (EmployeeInfoEntity employeeInfoEntity : resultList) {
            RegularDto regularDto = BeanUtilsMabach.doToDto(employeeInfoEntity, RegularDto.class);
            Long jobId = regularDto.getJobId();
            String jobName = jobController.getJobName(jobId);
            regularDto.setJobname(jobName); //岗位名称
            Long jobLevelId = regularDto.getJobLevelId();
            String jobLevelNameById = jobController.getJobLevelNameById(jobLevelId);
            regularDto.setJobLevel(jobLevelNameById); //职级
            Long orgId = regularDto.getOrgId();
            String orgNameById = jobController.getOrgNameById(orgId);
            regularDto.setOrgname(orgNameById); //公司
            Long unitId = regularDto.getUnitId();
            String unitName = jobController.getOrgNameById(unitId);
            regularDto.setUnitname(unitName); //部门
            Date regularDay = employeeInfoEntity.getRegularDay();
            Integer probationday = employeeInfoEntity.getProbationday();
            Date hiredate = employeeInfoEntity.getHiredate();
            if (ObjectUtils.isEmpty(regularDay)) {
                if (!ObjectUtils.isEmpty(probationday)) {
                    regularDay = DateUtils.addDateMonths(hiredate, probationday);
                    regularDto.setRegularDay(regularDay); //预计转正日期
                }
            }
            dtos.add(regularDto);

        }


        XSSFWorkbook wb = ExcelUtil.exportExcel3(null, dtos, RegularDto.class, false);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "公积金方案报表";
        outputFile2007(response, wb, excelName);

    }

    // 社保人员导出
    @GetMapping("/ExportSecurityEmployeeList")
    @ApiOperation("导出-社保方案报表")
    @PreAuthorize("hasAuthority('emp:security:update')")
    public void exportSecurityEmployeeList(HttpServletResponse response, @RequestParam(value = "securityId", required = false) String securityId, HttpServletRequest request) throws Exception {
        //验证是否有用户权限
        //获取当前用户角色下合同主体Id
        R checkR = loginController.checkContractPermission(request);
        if (!R.isOk.equals(checkR.getStatus())) {
            return;
        }
        Long security = null;
        if (!StringUtils.isEmpty(securityId)) {
            security = Long.parseLong(securityId);
        }
        List<EmployeeSecurityDto> list = securityDao.getUserSecurityList(security);

//        if (!CollectionUtils.isEmpty(list)) {
//            for (EmployeeSecurityDto item : list) {
//
//            }
//        }

        XSSFWorkbook wb = ExcelUtil.exportExcel2(null, 2,list, EmployeeSecurityDto.class, false);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String excelName = "社保方案人员列表";
        outputFile2007(response, wb, excelName);
    }



}